# -*- coding: utf-8 -*-
'''
Created on Sep 9, 2016

@author: hw
'''
import json
import pandas as pd
from settings import DATABASES
import MySQLdb
from sqlalchemy import create_engine
from _codecs import encode
from sqlalchemy.sql.sqltypes import *
from werkzeug.urls import BaseURL
try:
    from urllib.request import urlopen, Request
except ImportError:
    from urllib2 import urlopen, Request 

'''
除权除息因子处理
'''
class DividendData(object):
    '''
    classdocs
    '''
    
    def __init__(self, **kwargs):
        '''
        Constructor
        '''
        self.url = kwargs.get("url", "http://172.126.8.200/cw/interest/data/")
    
    def SaveToDb(self):
        BaseUrl = "http://172.16.8.200/cw/interest/data/"
        engine = create_engine(DATABASES["hqdata"].get("engine") + "://" + DATABASES["hqdata"].get("user") + ":" + DATABASES["hqdata"].get("password") + "@" + DATABASES["hqdata"].get("host") + "/" + DATABASES["hqdata"].get("db") + "?charset=utf8")
        con = MySQLdb.connect(host=DATABASES["hqdata"].get("host"), user=DATABASES["hqdata"].get("user"), passwd=DATABASES["hqdata"].get("password"), db=DATABASES["hqdata"].get("db"), charset="utf8")
        sql = 'select  concat((case market when "XSHG" then "01" else "00" end ),code) as stockcode from stock_baseinfo'
        codelist = pd.read_sql(sql, con)
        date = []  # 日期
        diviType = []  # 变动类型,1-派息，2-送股，3-转增，4-配股，5-公开增发，6-非公开增发
        cb = []  # 每10股派现
        bs = []  # 每10股送股
        cs = []  # 每10股转增
        apc = []  # 每10股配股数量
        apr = []  # 每10股配股价格
        isn = []  # 增发数量
        ipr = []  # 增发价格
        stock = []    
        for index in codelist.index:
            url = BaseUrl + codelist.iloc[index].stockcode
            request = Request(url)
            text = urlopen(request, timeout=30).read()
            strJson = json.loads(text, encoding='GBK')
            if strJson == None:
                continue
            for dividend in strJson:
                tmp = dividend.get("sc")
                market = tmp[:2]
                tmpcode = ""
                if market == "00":
                    tmpcode = tmp[2:] + ".XSHE"
                elif market == "01":
                    tmpcode = tmp[2:] + ".XSHG"    
                stock.append(tmpcode)
                date.append(dividend.get("cd"))
                diviType.append(int(dividend.get("crp")))
                cb.append(float(dividend.get("cb", 0.00)))
                bs.append(float(dividend.get("bs", 0.00)))
                cs.append(float(dividend.get("cs", 0.00)))
                apc.append(float(dividend.get("apc", 0.00)))
                apr.append(float(dividend.get("apr", 0.00)))
                isn.append(float(dividend.get("isn", 0.00)))
                ipr.append(float(dividend.get("ipr", 0.00)))
        df = pd.DataFrame(index=[stock, date], data={"divitype":diviType, "dividendCash":cb, "dividendNum":bs, "trunNum":cs, "rationedPrice":apr, "rationedNum":apc, "seoNum":isn, "seoPrice":ipr})
        df.index.names = ["code", "date"]
        dttype = {"code":VARCHAR(32), "date":Date, "diviType":INT}
        df.to_sql('stock_divident_factor', engine, if_exists='replace', dtype=dttype)
        
if __name__ == '__main__':
#         BaseUrl = "http://172.16.8.200/cw/interest/data/"
        BaseUrl = "http://mobnews.999finpro.com/cw/interest/data/"
        engine = create_engine(DATABASES["hqdata"].get("engine") + "://" + DATABASES["hqdata"].get("user") + ":" + DATABASES["hqdata"].get("password") + "@" + DATABASES["hqdata"].get("host") + "/" + DATABASES["hqdata"].get("db") + "?charset=utf8")
        con = MySQLdb.connect(host=DATABASES["hqdata"].get("host"), user=DATABASES["hqdata"].get("user"), passwd=DATABASES["hqdata"].get("password"), db=DATABASES["hqdata"].get("db"), charset="utf8")
        sql = 'select  concat((case market when "XSHG" then "01" else "00" end ),code) as stockcode from stock_baseinfo'
        codelist = pd.read_sql(sql, con)
        date = []  # 日期
        diviType = []  # 变动类型,1-派息，2-送股，3-转增，4-配股，5-公开增发，6-非公开增发
        cb = []  # 每10股派现
        bs = []  # 每10股送股
        cs = []  # 每10股转增
        apc = []  # 每10股配股数量
        apr = []  # 每10股配股价格
        isn = []  # 增发数量
        ipr = []  # 增发价格
        stock = []    
        for index in codelist.index:
            url = BaseUrl + codelist.iloc[index].stockcode
            request = Request(url)
            text = urlopen(request, timeout=30).read()
            strJson = json.loads(text, encoding='GBK')
            if strJson == None:
                continue
            for dividend in strJson:
                tmp = dividend.get("sc")
                market = tmp[:2]
                tmpcode = ""
                if market == "00":
                    tmpcode = tmp[2:] + ".XSHE"
                elif market == "01":
                    tmpcode = tmp[2:] + ".XSHG"    
                stock.append(tmpcode)
                date.append(dividend.get("cd"))
                diviType.append(int(dividend.get("crp")))
                cb.append(float(dividend.get("cb", 0.00)))
                bs.append(float(dividend.get("bs", 0.00)))
                cs.append(float(dividend.get("cs", 0.00)))
                apc.append(float(dividend.get("apc", 0.00)))
                apr.append(float(dividend.get("apr", 0.00)))
                isn.append(float(dividend.get("isn", 0.00)))
                ipr.append(float(dividend.get("ipr", 0.00)))
        df = pd.DataFrame(index=[stock, date], data={"divitype":diviType, "dividendCash":cb, "dividendNum":bs, "trunNum":cs, "rationedPrice":apr, "rationedNum":apc, "seoNum":isn, "seoPrice":ipr})
        df.index.names = ["code", "date"]
        dttype = {"code":VARCHAR(32), "date":Date, "diviType":INT}
        df.to_sql('stock_divident_factor', engine, if_exists='replace', dtype=dttype)
